package com.sh3h.generator.web.utils;


import com.sh3h.generator.web.domain.GenTable;
import com.sh3h.generator.web.domain.query.entity.TableFieldQuery;
import com.sh3h.generator.web.domain.query.entity.TableQuery;
import com.sh3h.generator.web.entity.TableEntity;
import com.sh3h.generator.web.entity.TableFieldEntity;
import org.springframework.util.StringUtils;

import java.rmi.ServerException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class GenUtils {
    private static final int CONNECTION_TIMEOUTS_SECONDS = 6;

    public static List<TableEntity> getTableList(TableQuery tableQuery) throws SQLException, ClassNotFoundException {
        List<TableEntity> tableList = new ArrayList<>();
        Connection connection = DbUtils.getConnection(tableQuery.getDriverClass(), tableQuery.getConnUrl(), tableQuery.getUsername(), tableQuery.getPassword());
        try {
            //查询数据
            PreparedStatement preparedStatement = connection.prepareStatement(tableQuery.getQuery().tableSql(null));
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                TableEntity entity = new TableEntity();
                entity.setTableComment(rs.getString(tableQuery.getQuery().tableComment()));
                entity.setTableName(rs.getString(tableQuery.getQuery().tableName()));
                tableList.add(entity);
            }

            connection.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        return tableList;
    }

    public static TableEntity getTable(TableQuery tableQuery, String tableName) throws ServerException, SQLException, ClassNotFoundException {
        Connection connection = DbUtils.getConnection(tableQuery.getDriverClass(), tableQuery.getConnUrl(), tableQuery.getUsername(), tableQuery.getPassword());
        try {
            // 查询数据
            PreparedStatement preparedStatement = connection.prepareStatement(getTableSQL(tableQuery.getDbType(), tableName));
            ResultSet rs = preparedStatement.executeQuery();
            if (rs.next()) {
                TableEntity tb = new TableEntity();
                tb.setTableName(rs.getString(tableQuery.getQuery().tableName()));
                tb.setTableComment(rs.getString(tableQuery.getQuery().tableComment()));
                return tb;
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

        throw new ServerException("数据表不存在：" + tableName);
    }

    public static List<TableFieldEntity> getTableFieldList(TableQuery tableQuery, String tableId, String tableName) throws SQLException, ClassNotFoundException {
        List<TableFieldEntity> tableFieldList = new ArrayList<>();
        Connection connection = DbUtils.getConnection(tableQuery.getDriverClass(), tableQuery.getConnUrl(), tableQuery.getUsername(), tableQuery.getPassword());
        try {
            String tableFieldsSql = tableQuery.getQuery().tableFieldsSql();
            if (tableQuery.getDbType() == "Oracle") {
                DatabaseMetaData md = connection.getMetaData();
                tableFieldsSql = String.format(tableFieldsSql.replace("#schema", md.getUserName()), tableName);
            } else {
                tableFieldsSql = String.format(tableFieldsSql, tableName);
            }
            PreparedStatement preparedStatement = connection.prepareStatement(tableFieldsSql);
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                TableFieldEntity field = new TableFieldEntity();
                field.setTableId(tableId);
                field.setFieldName(rs.getString(tableQuery.getQuery().fieldName()));
                String fieldType = rs.getString(tableQuery.getQuery().fieldType());
                if (fieldType.contains(" ")) {
                    fieldType = fieldType.substring(0, fieldType.indexOf(" "));
                }
                field.setFieldType(fieldType);
                field.setFieldComment(rs.getString(tableQuery.getQuery().fieldComment()));
                String key = rs.getString(tableQuery.getQuery().fieldKey());
                field.setPrimaryPk(StringUtils.isEmpty(key) && "PRI".equalsIgnoreCase(key) == true ? 1 : 0);

                tableFieldList.add(field);
            }
        } catch (Exception e) {
//            log.error(e.getMessage(), e);
        }

        return tableFieldList;
    }

    public static String getTableSQL(String dbType, String tableName) {
        StringBuilder sql = new StringBuilder();
        switch (dbType) {
            case "MySql":
                sql.append("select table_name, table_comment from information_schema.tables ");
                sql.append("where table_schema = (select database()) ");
                // 表名查询
                if (!StringUtils.isEmpty(tableName)) {
                    sql.append("and table_name = '").append(tableName).append("' ");
                }
                sql.append("order by table_name asc");
                break;
            case "SqlServer":
                sql.append("select cast(so.name as varchar(500)) as TABLE_NAME, cast(sep.value as varchar(500)) as COMMENTS from sysobjects so ");
                sql.append("left JOIN sys.extended_properties sep on sep.major_id=so.id and sep.minor_id=0 where (xtype='U' or xtype='V') ");

                // 表名查询
                if (!StringUtils.isEmpty(tableName)) {
                    sql.append("and cast(so.name as varchar(500)) = '").append(tableName).append("' ");
                }
                sql.append(" order by cast(so.name as varchar(500))");
                break;
            case "Oracle":
                sql.append("select dt.table_name, dtc.comments from user_tables dt,user_tab_comments dtc ");
                sql.append("where dt.table_name = dtc.table_name ");
                // 表名查询
                if (!StringUtils.isEmpty(tableName)) {
                    sql.append("and dt.table_name = '").append(tableName).append("' ");
                }
                sql.append("order by dt.table_name asc");
                break;
            case "DM":
                sql.append("SELECT T.* FROM (SELECT DISTINCT T1.TABLE_NAME AS TABLE_NAME,T2.COMMENTS AS TABLE_COMMENT FROM USER_TAB_COLUMNS T1 ");
                sql.append("INNER JOIN USER_TAB_COMMENTS T2 ON T1.TABLE_NAME = T2.TABLE_NAME) T WHERE 1=1 ");
                // 表名查询
                if (!StringUtils.isEmpty(tableName)) {
                    sql.append("and T.TABLE_NAME = '").append(tableName).append("' ");
                }
                sql.append("order by T.TABLE_NAME asc");
                break;
            default:
                break;
        }
        return sql.toString();
    }
}